package pers.qianyu.module.recommend.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import pers.qianyu.module.core.domain.recommend.dataobj.UserLabelPreferenceDO;

import java.util.List;

/**
 * @author mizzle rain
 * @date 2021-05-13 15:45
 */
@Mapper
public interface RecommendDao {
    @Select("select t.user_id, t.label_id, sum(t.`count`) `count` from (\n" +
            "\n" +
            "select liv.created_by user_id, lil.label_id label_id, liv.`count` `count` from lamb_image_view liv \n" +
            "left join lamb_image_label lil on  liv.image_id = lil.image_id\n" +
            "where lil.deleted = 0 and liv.deleted = 0\n" +
            "\n" +
            "union all \n" +
            "\n" +
            "select lil.created_by user_id, lil2.label_id label_id, 5 as `count` from lamb_image_like lil\n" +
            "left join lamb_image_label lil2 on  lil.image_id = lil2.image_id\n" +
            "where lil.deleted = 0 and lil2.deleted = 0\n" +
            "\n" +
            "union all \n" +
            "\n" +
            "select lic.created_by user_id, lil.label_id label_id, 5 as `count` from lamb_image_collection lic \n" +
            "left join lamb_image_label lil on lic.image_id = lil.image_id \n" +
            "where lil.deleted = 0 and lic.deleted = 0\n" +
            "\n" +
            ") as t group by t.user_id, t.label_id;")
    List<UserLabelPreferenceDO> findUserLabelPreferenceList();

/*
select t.user_id, t.label_id, sum(t.`count`) `count` from (

select liv.created_by user_id, lil.label_id label_id, liv.`count` `count` from lamb_image_view liv
left join lamb_image_label lil on  liv.image_id = lil.image_id
where lil.deleted = 0 and liv.deleted = 0

union all

select lil.created_by user_id, lil2.label_id label_id, 5 as `count` from lamb_image_like lil
left join lamb_image_label lil2 on  lil.image_id = lil2.image_id
where lil.deleted = 0 and lil2.deleted = 0

union all

select lic.created_by user_id, lil.label_id label_id, 5 as `count` from lamb_image_collection lic
left join lamb_image_label lil on lic.image_id = lil.image_id
where lil.deleted = 0 and lic.deleted = 0

) as t group by t.user_id, t.label_id;
*/
}
